﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Agresso.Interface.Layer.SQL
{
    public class StatementSQL
    {
        public static string GetAllSoftmerStatement()
        {
            return @"
            Select distinct 
                vVMheadopfix.VMHeadType headfixture_type,
                vVMHeadOpfix.VesselName head_fixture_name,
                vOpOrg9Srch.KeyName profit_centre,
                vStmtSrch.Header statement_header,
                vVMHeadOpfix.SellerKeyName registered_owner,
                vVesselSrch.Name vessel_name,
                vHeadFixVoyage.VoyageNo voyage_no,
                vVMHeadOpfix.DisponentOwner disponent_owner,
                vVMHeadOpfix.EnteredBy created_by,
                vStmtSrch.InvoiceNo statement_number,
                vStmtSrch.DocInvDate statement_date,
                vStmtSrch.DueDate statement_due_date,
                vOpHeadFixChargeSrch.Invoiced statement_issued,
                vStmtSrch.Curr statement_currency,
                vVMRemit.RemittanceAmount remittance_amount,
                vOpStmtSrch.Narrative charge_description,
                vVMHeadOpfix.StartVoyNo start_voyage_no,
                vStmtSrch.Type statement_type,
                vOpStmtSrch.ChargeType charge_type,
                vStmtSrch.Company company,
                vStmtSrch.RaisedToFinalBy raised_to_final_by,
                vHeadFixVoyage.Reference voyage_ref,
                vStmtSrch.InvoiceTotal statement_total,
                 vOpStmtSrch.InvChargeAmount charge_amount
                 FROM VVMHEADOPFIX JOIN VOPSTMTSRCH ON VVMHEADOPFIX.ID = VOPSTMTSRCH.FIX 
                 JOIN VSTMTSRCH ON VVMHEADOPFIX.ID = VSTMTSRCH.FIX AND 
                 VSTMTSRCH.FIX=VOPSTMTSRCH.FIX AND 
                 VSTMTSRCH.OPNO=VOPSTMTSRCH.OPNO AND 
                 VSTMTSRCH.VYNO=VOPSTMTSRCH.VYNO AND 
                 VSTMTSRCH.GRNO=VOPSTMTSRCH.GRNO AND 
                 VSTMTSRCH.NO=VOPSTMTSRCH.NO 
                 JOIN VVESSELSRCH ON VVMHEADOPFIX.VESSELID = VVESSELSRCH.ID 
                 JOIN VOPORG9SRCH ON VVMHEADOPFIX.COMPANY=VOPORG9SRCH.ID 
                 JOIN VOPHEADFIXCHARGESRCH ON VVMHEADOPFIX.ID=VOPHEADFIXCHARGESRCH.FIXTURE 
                 LEFT OUTER JOIN VVMREMIT ON VVMHEADOPFIX.ID = VVMREMIT.FIXTURE AND 
                 VSTMTSRCH.GRNO = VVMREMIT.GROUPNO AND 
                 VSTMTSRCH.FIX = VVMREMIT.FIXTURE AND 
                 VSTMTSRCH.OPNO = VVMREMIT.OPTIONNO AND 
                 VSTMTSRCH.VYNO = VVMREMIT.VOYAGENO AND 
                 VSTMTSRCH.NO = VVMREMIT.NO 
                 LEFT OUTER JOIN VHEADFIXVOYAGE ON VVMHEADOPFIX.VMID = VHEADFIXVOYAGE.ID 
                 WHERE vVMHeadOpfix.vmStatus NOT IN ('UnFixed','Unfixed COA Lifting','Cargo Booking') 
                 --AND (vStmtSrch.InvoiceNo like '%3232%') 
                 AND 
                 (vOpHeadFixChargeSrch.Invoiced <> 1)
                 AND vStmtSrch.DocInvDate  > '2013-03-01 00:00' 
            ";
        }
        public static string GetAllSoftmerStatementIgnoreVoyage_REV01()
        {
            return @" 
	                 select distinct 
	                vVMheadopfix.VMHeadType headfixture_type,
                vVMHeadOpfix.VesselName head_fixture_name,
                vVMHeadOpfix.SellerKeyName registered_owner,
                vVMHeadOpfix.DisponentOwner disponent_owner,
                vVMHeadOpfix.StartVoyNo start_voyage_no,
                vVMHeadOpfix.EnteredBy created_by,
                vVMHeadOpfix.VesselName vessel_name,
                vVMHeadOpfix.VesselID vessel_code,
                vOpOrg9Srch.KeyName profit_centre,
                Notes voyage_no,
                vStmtSrch.Header statement_header,
                vStmtSrch.InvoiceNo statement_number,
	                vStmtSrch.DocInvDate statement_date,
	                vStmtSrch.DueDate statement_due_date,
	                vStmtSrch.Curr statement_currency,
	                vStmtSrch.Type statement_type,
	                vStmtSrch.Company company,
	                vStmtSrch.RaisedToFinalBy raised_to_final_by,
	                vStmtSrch.InvoiceTotal statement_total,
	                vOpHeadFixChargeSrch.Invoiced statement_issued,
	                0 remittance_amount,--vVMRemit.RemittanceAmount remittance_amount,
	                VOPINVOICELINENARRATIVE.Narrative charge_description,
	                VOPINVOICELINENARRATIVE.Type charge_type,
	                VOPINVOICELINENARRATIVE.Charge charge_amount,
                0 charge_address_amount,
                0 charge_co_comm_amount,    
                '' voyage_ref,
                VOPINVOICELINENARRATIVE.NarrativeLine narrative_line,
                VOPINVOICELINENARRATIVE.ChargeID charge_id,
                vVMHeadOpfix.Broker fixed_by
                 from vStmtSrch 
	                 join VVMHEADOPFIX on 
	                VVMHEADOPFIX.ID = VSTMTSRCH.FIX 
	                join VOPINVOICELINENARRATIVE on
	                VOPINVOICELINENARRATIVE.InvoiceNo = VSTMTSRCH.No
	                and  VOPINVOICELINENARRATIVE.Fixture = VSTMTSRCH.Fix and
	                VOPINVOICELINENARRATIVE.InvoiceNo = VSTMTSRCH.No and
	                VOPINVOICELINENARRATIVE.Charge is not null  and
	                VOPINVOICELINENARRATIVE.VoyageNo =  VSTMTSRCH.VyNo and
	                VOPINVOICELINENARRATIVE.GroupNo = VSTMTSRCH.GrNo and
	                VOPINVOICELINENARRATIVE.OptionNo = VSTMTSRCH.OpNo
	                JOIN VOPORG9SRCH ON VVMHEADOPFIX.COMPANY=vOpOrg9Srch.ID 
	                JOIN VOPHEADFIXCHARGESRCH ON VVMHEADOPFIX.ID=VOPHEADFIXCHARGESRCH.FIXTURE 
	                join VOPCHARGEFIXTURESRCH on VOPCHARGEFIXTURESRCH.ID = VOPINVOICELINENARRATIVE.ChargeID
	                LEFT OUTER JOIN VVMREMIT ON VVMHEADOPFIX.ID = VVMREMIT.FIXTURE AND 
                 VSTMTSRCH.GRNO = VVMREMIT.GROUPNO AND 
                 VSTMTSRCH.FIX = VVMREMIT.FIXTURE AND 
                 VSTMTSRCH.OPNO = VVMREMIT.OPTIONNO AND 
                 VSTMTSRCH.VYNO = VVMREMIT.VOYAGENO AND 
                 VSTMTSRCH.NO = VVMREMIT.NO 
	                 where 
	                 --vOpHeadFixChargeSrch.Invoiced <> 1-- all pushed in and -----------------19092013
	                 vVMHeadOpfix.vmStatus NOT IN ('UnFixed','Unfixed COA Lifting','Cargo Booking') 
	                  AND vStmtSrch.DocInvDate  > '2013-07-23 00:00' 
                 AND vStmtSrch.InvoiceNo <> 'proforma'
                 AND vStmtSrch.InvoiceNo not like 'PPL%'
                 AND  vOpOrg9Srch.KeyName <> 'PACNAV'
                 AND vStmtSrch.Header <> 'Commission Credit Statement'
                 AND vStmtSrch.Header <> 'Commission Statement'
                 --and vStmtSrch.InvoiceNo = 'PCLS/10586'
                 --and vStmtSrch.Type <> 'Charge Credit'
                 --and VOPINVOICELINENARRATIVE.NarrativeLine = 80
                and vOpOrg9Srch.KeyName <> 'PACC UK'
                and vOpHeadFixChargeSrch.Invoiced = @invoiced
                or
                (
                vVMHeadOpfix.vmStatus NOT IN ('UnFixed','Unfixed COA Lifting','Cargo Booking') 
                AND vStmtSrch.DocInvDate  > '2014-01-01 00:00' 
                AND vStmtSrch.InvoiceNo <> 'proforma'
                AND vStmtSrch.InvoiceNo not like 'PPL%'
                AND  vOpOrg9Srch.KeyName <> 'PACNAV'
                AND vStmtSrch.Header <> 'Commission Credit Statement'
                AND vStmtSrch.Header <> 'Commission Statement'
                and vOpOrg9Srch.KeyName = 'PACC UK'
                and vOpHeadFixChargeSrch.Invoiced = @invoiced
                )
            ";
        }
        public static string GetAllSoftmerStatementIgnoreVoyage_REV01_byInvoice()
        {
            return @" 
	                 select distinct 
	                vVMheadopfix.VMHeadType headfixture_type,
                vVMHeadOpfix.VesselName head_fixture_name,
                vVMHeadOpfix.SellerKeyName registered_owner,
                vVMHeadOpfix.DisponentOwner disponent_owner,
                vVMHeadOpfix.StartVoyNo start_voyage_no,
                vVMHeadOpfix.EnteredBy created_by,
                vVMHeadOpfix.VesselName vessel_name,
                vVMHeadOpfix.VesselID vessel_code,
                vOpOrg9Srch.KeyName profit_centre,
                Notes voyage_no,
                vStmtSrch.Header statement_header,
                vStmtSrch.InvoiceNo statement_number,
	                vStmtSrch.DocInvDate statement_date,
	                vStmtSrch.DueDate statement_due_date,
	                vStmtSrch.Curr statement_currency,
	                vStmtSrch.Type statement_type,
	                vStmtSrch.Company company,
	                vStmtSrch.RaisedToFinalBy raised_to_final_by,
	                vStmtSrch.InvoiceTotal statement_total,
	                vOpHeadFixChargeSrch.Invoiced statement_issued,
	                0 remittance_amount,--vVMRemit.RemittanceAmount remittance_amount,
	                VOPINVOICELINENARRATIVE.Narrative charge_description,
	                VOPINVOICELINENARRATIVE.Type charge_type,
	                VOPINVOICELINENARRATIVE.Charge charge_amount,
                0 charge_address_amount,
                0 charge_co_comm_amount,    
                '' voyage_ref,
                VOPINVOICELINENARRATIVE.NarrativeLine narrative_line,
                VOPINVOICELINENARRATIVE.ChargeID charge_id,
                vVMHeadOpfix.Broker fixed_by
                 from vStmtSrch 
	                 join VVMHEADOPFIX on 
	                VVMHEADOPFIX.ID = VSTMTSRCH.FIX 
	                join VOPINVOICELINENARRATIVE on
	                VOPINVOICELINENARRATIVE.InvoiceNo = VSTMTSRCH.No
	                and  VOPINVOICELINENARRATIVE.Fixture = VSTMTSRCH.Fix and
	                VOPINVOICELINENARRATIVE.InvoiceNo = VSTMTSRCH.No and
	                VOPINVOICELINENARRATIVE.Charge is not null  and
	                VOPINVOICELINENARRATIVE.VoyageNo =  VSTMTSRCH.VyNo and
	                VOPINVOICELINENARRATIVE.GroupNo = VSTMTSRCH.GrNo and
	                VOPINVOICELINENARRATIVE.OptionNo = VSTMTSRCH.OpNo
	                JOIN VOPORG9SRCH ON VVMHEADOPFIX.COMPANY=vOpOrg9Srch.ID 
	                JOIN VOPHEADFIXCHARGESRCH ON VVMHEADOPFIX.ID=VOPHEADFIXCHARGESRCH.FIXTURE 
	                join VOPCHARGEFIXTURESRCH on VOPCHARGEFIXTURESRCH.ID = VOPINVOICELINENARRATIVE.ChargeID
	                LEFT OUTER JOIN VVMREMIT ON VVMHEADOPFIX.ID = VVMREMIT.FIXTURE AND 
                 VSTMTSRCH.GRNO = VVMREMIT.GROUPNO AND 
                 VSTMTSRCH.FIX = VVMREMIT.FIXTURE AND 
                 VSTMTSRCH.OPNO = VVMREMIT.OPTIONNO AND 
                 VSTMTSRCH.VYNO = VVMREMIT.VOYAGENO AND 
                 VSTMTSRCH.NO = VVMREMIT.NO 
	                 where 
	             --vOpHeadFixChargeSrch.Invoiced <> 1-- all pushed in and -----------------19092013
	             vVMHeadOpfix.vmStatus NOT IN ('UnFixed','Unfixed COA Lifting','Cargo Booking') 
                 AND vStmtSrch.Header <> 'Commission Credit Statement'
                 AND vStmtSrch.Header <> 'Commission Statement'
                 and vStmtSrch.InvoiceNo = @InvoiceNo
                 --and vStmtSrch.Type <> 'Charge Credit'
                 --and VOPINVOICELINENARRATIVE.NarrativeLine = 80
                and vOpHeadFixChargeSrch.Invoiced = @invoiced
            ";
        }
        public static string GetAllSoftmerStatementIgnoreVoyage()
        {
            return @"
            Select distinct 
                vVMheadopfix.VMHeadType headfixture_type,
                vVMHeadOpfix.VesselName head_fixture_name,
                vOpOrg9Srch.KeyName profit_centre,
                vStmtSrch.Header statement_header,
                vVMHeadOpfix.SellerKeyName registered_owner,
                vVesselSrch.Name vessel_name,
                --vHeadFixVoyage.VoyageNo voyage_no,
                Notes voyage_no,
                vVMHeadOpfix.DisponentOwner disponent_owner,
                vVMHeadOpfix.EnteredBy created_by,
                vStmtSrch.InvoiceNo statement_number,
                vStmtSrch.DocInvDate statement_date,
                vStmtSrch.DueDate statement_due_date,
                vOpHeadFixChargeSrch.Invoiced statement_issued,
                vStmtSrch.Curr statement_currency,
                vVMRemit.RemittanceAmount remittance_amount,
                vOpStmtSrch.Narrative charge_description,
                vVMHeadOpfix.StartVoyNo start_voyage_no,
                vStmtSrch.Type statement_type,
                vOpStmtSrch.ChargeType charge_type,
                vStmtSrch.Company company,
                vStmtSrch.RaisedToFinalBy raised_to_final_by,
                --vHeadFixVoyage.Reference voyage_ref,
                '' voyage_ref,
                vStmtSrch.InvoiceTotal statement_total,
                 vOpStmtSrch.InvChargeAmount charge_amount,
                vOpStmtSrch.InvChargeAddrComm charge_address_amount,
                vOpStmtSrch.InvChargeCoComm charge_co_comm_amount
                 FROM VVMHEADOPFIX JOIN VOPSTMTSRCH ON VVMHEADOPFIX.ID = VOPSTMTSRCH.FIX 
                 JOIN VSTMTSRCH ON VVMHEADOPFIX.ID = VSTMTSRCH.FIX AND 
                 VSTMTSRCH.FIX=VOPSTMTSRCH.FIX AND 
                 VSTMTSRCH.OPNO=VOPSTMTSRCH.OPNO AND 
                 VSTMTSRCH.VYNO=VOPSTMTSRCH.VYNO AND 
                 VSTMTSRCH.GRNO=VOPSTMTSRCH.GRNO AND 
                 VSTMTSRCH.NO=VOPSTMTSRCH.NO 
                 JOIN VVESSELSRCH ON VVMHEADOPFIX.VESSELID = VVESSELSRCH.ID 
                 JOIN VOPORG9SRCH ON VVMHEADOPFIX.COMPANY=VOPORG9SRCH.ID 
                 JOIN VOPHEADFIXCHARGESRCH ON VVMHEADOPFIX.ID=VOPHEADFIXCHARGESRCH.FIXTURE 
                 LEFT OUTER JOIN VVMREMIT ON VVMHEADOPFIX.ID = VVMREMIT.FIXTURE AND 
                 VSTMTSRCH.GRNO = VVMREMIT.GROUPNO AND 
                 VSTMTSRCH.FIX = VVMREMIT.FIXTURE AND 
                 VSTMTSRCH.OPNO = VVMREMIT.OPTIONNO AND 
                 VSTMTSRCH.VYNO = VVMREMIT.VOYAGENO AND 
                 VSTMTSRCH.NO = VVMREMIT.NO 
                 LEFT OUTER JOIN VHEADFIXVOYAGE ON VVMHEADOPFIX.VMID = VHEADFIXVOYAGE.ID 
                 WHERE vVMHeadOpfix.vmStatus NOT IN ('UnFixed','Unfixed COA Lifting','Cargo Booking') 
                 --AND vStmtSrch.InvoiceNo in ('pcls/6504' , 'pcls/6505') 
                 AND 
                 (vOpHeadFixChargeSrch.Invoiced <> 1)
                 AND vStmtSrch.DocInvDate  > '2013-07-23 00:00' 
                 AND vStmtSrch.InvoiceNo <> 'proforma'
                 and vStmtSrch.InvoiceNo not like 'PPL%'
            ";
        }

        public static string CountStatementTemp()
        {
            return @"select COUNT(*) from i_statement where 
            statement_number = @statement_number
            and charge_description = @charge_description
            and charge_type = @charge_type
            and charge_amount=@charge_amount 
            and charge_address_amount = @charge_address_amount
            and charge_co_comm_amount = @charge_co_comm_amount
            ";

           // and   headfixture_type = @headfixture_type
           //and head_fixture_name = @head_fixture_name
           //and profit_centre = @profit_centre
           //and statement_header = @statement_header
           //and registered_owner = @registered_owner
           //and vessel_name = @vessel_name
           //and voyage_no = @voyage_no
           //and disponent_owner = @disponent_owner
           //and created_by = @created_by
           //     and statement_date = @statement_date
           //and statement_due_date = @statement_due_date
           //and statement_issued = @statement_issued
           //and statement_currency = @statement_currency
           //and remittance_amount = @remittance_amount
           //
           //and start_voyage_no = @start_voyage_no
           //and statement_type = @statement_type
           //
           //and company = @company
           //and raised_to_final_by = @raised_to_final_by
           //and voyage_ref = @voyage_ref
           //and statement_total = @statement_total
           //
        }

        public static string InsertStatementTemp()
        {
            return @"
            INSERT INTO [i_statement]
           ([headfixture_type]
           ,[head_fixture_name]
           ,[profit_centre]
           ,[statement_header]
           ,[registered_owner]
           ,[vessel_name]
           ,[voyage_no]
           ,[disponent_owner]
           ,[created_by]
           ,[statement_number]
           ,[statement_date]
           ,[statement_due_date]
           ,[statement_issued]
           ,[statement_currency]
           ,[remittance_amount]
           ,[charge_description]
           ,[start_voyage_no]
           ,[statement_type]
           ,[charge_type]
           ,[company]
           ,[raised_to_final_by]
           ,[voyage_ref]
           ,[statement_total]
           ,[pickup_flag]
           ,[insert_date]
            ,[charge_amount]
            ,[charge_address_amount]
            ,[charge_co_comm_amount]
            ,[narrative_line]
             ,[charge_id]
            ,[fixed_by]
            ,[vessel_code]
            )
     VALUES
           (
             @headfixture_type
           , @head_fixture_name
           , @profit_centre
           , @statement_header
           , @registered_owner
           , @vessel_name
           , @voyage_no
           , @disponent_owner
           , @created_by
           , @statement_number
           , @statement_date
           , @statement_due_date
           , @statement_issued
           , @statement_currency
           , @remittance_amount
           , @charge_description
           , @start_voyage_no
           , @statement_type
           , @charge_type
           , @company
           , @raised_to_final_by
           , @voyage_ref
           , @statement_total
            ,@pickup_flag
            ,@insert_date
            ,@charge_amount
            ,@charge_address_amount
            ,@charge_co_comm_amount
            ,@narrative_line
             ,@charge_id
            ,@fixed_by
            ,@vessel_code
        )
            ";
        }

        public static string GetPendingStatementInterface()
        {
            return @"select * from i_statement where pickup_flag = 0";
        }

        public static string GetPendingStatementInterfaceByStatementNumber()
        {
            return @"select * from i_statement where pickup_flag = 0 and statement_number = @statement_number  and statement_date > '2013-07-23'";
           // return @"select * from i_statement where statement_number = @statement_number";
        }



        public static string GetMaxVoyageNoByStatementNo()
        {
            return @"   Select max(vHeadFixVoyage.VoyageNo) voyage_no
                 FROM VVMHEADOPFIX 
				 JOIN VOPSTMTSRCH ON VVMHEADOPFIX.ID = VOPSTMTSRCH.FIX 
                 JOIN VSTMTSRCH ON VVMHEADOPFIX.ID = VSTMTSRCH.FIX AND 
						 VSTMTSRCH.FIX=VOPSTMTSRCH.FIX AND 
						 VSTMTSRCH.OPNO=VOPSTMTSRCH.OPNO AND 
						 VSTMTSRCH.VYNO=VOPSTMTSRCH.VYNO AND 
						 VSTMTSRCH.GRNO=VOPSTMTSRCH.GRNO AND 
						 VSTMTSRCH.NO=VOPSTMTSRCH.NO                 
                 JOIN VOPHEADFIXCHARGESRCH ON VVMHEADOPFIX.ID=VOPHEADFIXCHARGESRCH.FIXTURE                
                 LEFT OUTER JOIN VHEADFIXVOYAGE ON VVMHEADOPFIX.VMID = VHEADFIXVOYAGE.ID 
                 WHERE vVMHeadOpfix.vmStatus NOT IN ('UnFixed','Unfixed COA Lifting','Cargo Booking') 
                --AND (vOpHeadFixChargeSrch.Invoiced <> 1)
                 AND vStmtSrch.InvoiceNo = @statement_number";
        }

        public static string GetMaxVoyageNoByVesselName()
        {
            return @"select max(vVMVoyageSrch.VoyageNo) from vVMVoyageSrch where VesselName = @vesselname";
        }

        public static string GetPendingAllStatementNumberInterface()
        {
            return @"select distinct(statement_number) from i_statement where pickup_flag = 0 and statement_date > '2013-07-23'";
           // return @"select distinct(statement_number) from i_statement where statement_number in ('PCLS/14328', 'PCLS/13838')";
        }

        public static string UpdateStatementPickup_flagStatus()
        {
            return @"update i_statement
            set 
            pickup_flag = @pickup_flag 
            , pickup_date = @pickup_date 
            , pickup_update_date=@pickup_update_date
            , agrtidList = @agrtidList
            , remarks = @remarks
            , batch_id = @batch_id
            where i_statement_id=@i_statement_id
               ";
        }

        public static string GetAllPendingStatement()
        {
            return @"select distinct(statement_number), statement_date, convert(char, pickup_date, 101) pickup_date, remarks 
from i_statement where pickup_flag = 0  order by statement_date";
        }

        public static string GetAllPendingStatementCount()
        {
            return @"select count(*) from i_statement where pickup_flag = 0";
        }

        public static string GetAllPendingStatement(string p)
        {
            return string.Format(@"select distinct(statement_number), statement_date, convert(char, pickup_date, 101) pickup_date, remarks from i_statement where pickup_flag = 0  
and statement_number +  replace(convert(char(11), statement_date, 106),' ','-')+ replace(convert(char(11), pickup_date, 106),' ','-')+ remarks like '%{0}%'
order by statement_date", p);
        }

        public static string GetStatementInvoiceMiddleLayer()
        {
            return "select distinct statement_number  from i_statement where pickup_flag in (0,1,2) and statement_number <> 'Proforma'";
        }
    }
}
